﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DevExpress.Xpo;
using System.Data;
using MySql.Data.MySqlClient;
namespace ChuanPlus.DAO
{
    class OrderDAO
    {
        //
        // save an order to database, stockCard, stock quantity, update customer's credit as 1 UnitOfWork
        //
        public static void save(Order order)
        {
            Session.DefaultSession.BeginTransaction();
            try
            {
                StockCard stCard;
                long idStockCard = StockCardDAO.GenerateId();
                TransactionType tran = TransactionTypeDAO.GetByName("Order");
                foreach (OrderDetails orderDetail in order.OrderDetails)
                {
                    // ================== TRU QUANTITY CUA SAN PHAM THEO ORDER DETAIL ===================                        
                    orderDetail.StockID.QtyBalance -= orderDetail.Quantity;
                    orderDetail.StockID.Save();
                    // ================ TRU HOA DON DO CUA TUNG SAN PHAM THEO ORDER DETAIL ==============
                    if (orderDetail.VAT > 0)
                    {
                        orderDetail.StockID.ID.RedInvQty -= orderDetail.Quantity;
                    }
                    // ================= TAO StockCard CHO TUNG SAN PHAM ================================
                    double Balance = orderDetail.StockID.QtyBalance;
                    stCard = new StockCard(++idStockCard, orderDetail.StockID, order.DeliveryDate, 0, orderDetail.Quantity, Balance, tran, order.OrderID);
                    stCard.Save();
                    orderDetail.StockID.ID.Save();
                }
                //============ CONG VO CUSTOMER CREDIT ==================================================                                                       
                order.CustomerID.Credit += order.Total;
                order.CustomerID.Save();
                order.Status = "P";
                order.Save();

                Session.DefaultSession.CommitTransaction();
                
            }
            catch (Exception ex)
            {
                Session.DefaultSession.RollbackTransaction();
                throw new Exception(ex.Message);
            }
        }
        //
        // update an order to database, stockCard, stock quantity update customer's credit as 1 UnitOfWork
        //
        public static bool update(Order order)
        {
            save(order);
            return true;
        }
        public static List<Order> getOrders(string status)
        {
            try
            {
                XPQuery<Order> order = new XPQuery<Order>(Session.DefaultSession);
                var list = from o in order
                           where o.Status == status
                           select o;
                return new List<Order>(list.AsEnumerable<Order>());
            }
            catch (Exception)
            {
                return null;
            }
        }

        //
        //return list of Order by OrderDate
        //order by OrderDate DESC
        public static DataTable GetOrderByDate(DateTime fromDate, DateTime toDate)
        {
            try
            {
                MySqlParameter[] param = new MySqlParameter[2];
                param[0] = new MySqlParameter("?fromDate", MySqlDbType.DateTime);
                param[0].Value = fromDate;

                param[1] = new MySqlParameter("?toDate", MySqlDbType.DateTime);
                param[1].Value = toDate;

                return DataProvider.execute("getOrderByDate", param);
            }
            catch (Exception)
            {
                return null;
            }
        }


        //
        //return list of Order by OrderDate base on OrderID
        // order by OrderDate DESC
        public static Order GetOrderByID(string OrderID)
        {
            try
            {
                XPQuery<Order> order = new XPQuery<Order>(Session.DefaultSession);
                var list = (from o in order
                            where (o.OrderID == OrderID)
                            select o).First();
                return list;
            }
            catch (Exception)
            {
                return null;
            }
        }
        /// <summary>
        /// Get Order with empty red invoce number and VAT in orderDetails > 0
        /// </summary>
        /// <returns></returns>
        public static DataTable GetEmptyRebInvoiceOrder()
        {
            try
            {
                return DataProvider.execute("getEmptyRebInvoiceOrder", null);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);                
            }
        }
	/// <summary>
        /// 
        /// </summary>
        /// <param name="d"></param>
        /// <returns></returns>
        public static DataTable GetSaleReport(DateTime fromDate, DateTime toDate, int distributor)
        {
            try
            {
                MySqlParameter[] param = new MySqlParameter[3];
                param[0] = new MySqlParameter("?fromDate", MySqlDbType.DateTime);
                param[0].Value = fromDate;

                param[1] = new MySqlParameter("?toDate", MySqlDbType.DateTime);
                param[1].Value = toDate;

                param[2] = new MySqlParameter("?distributor", MySqlDbType.Int32);
                param[2].Value = distributor;

                return DataProvider.execute("saleReport", param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
               
            }
        }
        //
        //return list of Order base on customer, start date, end date
        // order by OrderDate DESC
        public static DataTable GetOrderByCustomer(Customer customer, DateTime fromDate, DateTime toDate)
        {
            try
            {
                MySqlParameter[] param = new MySqlParameter[3];
                param[0] = new MySqlParameter("?fromDate", MySqlDbType.DateTime);
                param[0].Value = fromDate;

                param[1] = new MySqlParameter("?toDate", MySqlDbType.DateTime);
                param[1].Value = toDate;

                param[2] = new MySqlParameter("?idCustomer", MySqlDbType.Int32);
                param[2].Value = customer.CustomerID;

                return DataProvider.execute("getOrderByCustomer", param);
            }
            catch (Exception)
            {
                return null;
            }

        }
        //
        //return list of Order base on saleRep, start date, end date
        // order by OrderDate DESC
        public static DataTable GetOrderBySaleRep(Users user, DateTime fromDate, DateTime toDate)
        {
            try
            {
                MySqlParameter[] param = new MySqlParameter[3];
                param[0] = new MySqlParameter("?fromDate", MySqlDbType.DateTime);
                param[0].Value = fromDate;

                param[1] = new MySqlParameter("?toDate", MySqlDbType.DateTime);
                param[1].Value = toDate;

                param[2] = new MySqlParameter("?idSaleRep", MySqlDbType.Int32);
                param[2].Value = user.UserID;

                return DataProvider.execute("getOrderBySaleRep", param);
            }
            catch (Exception)
            {
                return null;
            }
        }
        /// <summary>
        /// Ham Tao id cho Order, gia tri tra ve la id max trog order
        /// </summary>
        /// <returns></returns>
        public static string GenerateId()
        {
            try
            {
                XPQuery<Order> order = new XPQuery<Order>(Session.DefaultSession);
                var list = (from o in order
                            select o).Max(p => p.OrderID);
                string temp = "000000" + (Convert.ToInt32(list) + 1).ToString();
                int offset = temp.Length - 6;                
                return temp.Substring(offset);
            }
            catch (Exception)
            {
                return "";
            }
        }
        public static bool cancelOrder(string orderID)
        {
            //Session.DefaultSession.BeginTransaction();
            //try
            //{

            //    Order order = OrderDAO.GetOrderByID(orderID);
            //    foreach (OrderDetails od in order.OrderDetails)
            //    {
            //        // ================== CAP NHAT LAI QUANTITY CUA SAN PHAM THEO ORDER DETAIL ===================                        
            //        od.StockID.QtyBalance += od.Quantity;
            //        od.StockID.Save();
            //        // ================ CAP NHAT LAI HOA DON DO CUA TUNG SAN PHAM THEO ORDER DETAIL ==============
            //        if (od.VAT > 0)
            //        {
            //            od.StockID.ID.RedInvQty += od.Quantity;
            //        }
            //        od.StockID.ID.Save();
            //    }
            //    //============ CAP NHAT LAI CUSTOMER CREDIT ==================================================                                                       
            //    order.CustomerID.Credit -= order.Total;
            //    order.CustomerID.Save();


            //    List<OrderDetails> lstOrderDetail = OrderDetailsDAO.GetOrderDetailByOrderId(order.OrderID);
            //    foreach (OrderDetails od in lstOrderDetail)
            //        od.Delete();

            //    List<StockCard> lstStockCard = StockCardDAO.getStockCardByOrderId(order.OrderID);
            //    foreach (StockCard s in lstStockCard)
            //    {
            //        s.Delete();
            //    }
            //    order.Delete();
            //    Session.DefaultSession.CommitTransaction();
            //    return true;
            //}
            //catch (Exception)
            //{
            //    Session.DefaultSession.RollbackTransaction();
            //    return false;
            //}
            return true;
        }
        public static void CalculateSubTotal(Order order)
        {
            foreach (OrderDetails item in order.OrderDetails)
            {
                item.SubTotal = (item.UnitPrice * item.Quantity) + item.VAT;
            }
        }


        public static List<PreviousPrice> GetPreviousSalePrice(string CustomerName, int ID)
        {
            Session s = new Session();
            XPQuery<OrderDetails> orderDetails = new XPQuery<OrderDetails>(s);
            var list = (from od in orderDetails
                       where od.OrderID.CustomerID.CustomerName == CustomerName && od.StockID.ID.ID == ID && od.OrderID.Status == "P"
                       orderby od.OrderID.OrderDate descending
                       select new PreviousPrice(od.OrderID.OrderDate, od.UnitPrice)).Take(3);
            return new List<PreviousPrice>(list.AsEnumerable<PreviousPrice>());
        }
    }
    public class PreviousPrice
    {
        private DateTime _OrderDate;
        public DateTime OrderDate
        {
            get { return _OrderDate; }
            set { _OrderDate = value; }
        }
        private double _UnitPrice;
        public double UnitPrice
        {
            get { return _UnitPrice; }
            set { _UnitPrice = value; }
        }
        public PreviousPrice()
        {

        }
        /// <summary>
        /// Initializes a new instance of the PreviousSalePrice class.
        /// </summary>
        /// <param name="orderDate"></param>
        /// <param name="unitPrice"></param>
        public PreviousPrice(DateTime orderDate, double unitPrice)
        {
            _OrderDate = orderDate;
            _UnitPrice = unitPrice;
        }
    }
}
